package com.team9.fitness.mapper;

import com.team9.fitness.entity.PostComment;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

/**
 * 帖子评论数据访问层接口
 * 
 * 提供帖子评论数据的CRUD操作：
 * - 评论管理：插入、更新、删除评论
 * - 查询功能：支持按帖子、用户、ID查询，支持排序
 * - 统计功能：统计评论的点赞数量
 * - 状态管理：支持用户点赞状态查询
 */
@Mapper
public interface PostCommentMapper {

    @Insert("INSERT INTO post_comment (post_id, user_id, content, like_count, parent_id, root_id, created_at) " +
            "VALUES (#{postId}, #{userId}, #{content}, #{likeCount}, #{parentId}, #{rootId}, #{createdAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(PostComment comment);

    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.post_id = #{postId} AND pc.parent_id IS NULL ORDER BY pc.created_at ASC")
    List<PostComment> selectByPostIdWithStatsOrderByCreatedAtAsc(Long postId, Long currentUserId);

    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.user_id = #{userId} ORDER BY pc.created_at DESC")
    List<PostComment> selectByUserIdWithStatsOrderByCreatedAtDesc(Long userId, Long currentUserId);

    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.user_id = #{userId} ORDER BY pc.created_at DESC LIMIT #{size} OFFSET #{offset}")
    List<PostComment> selectByUserIdWithStatsOrderByCreatedAtDescPaged(Long userId, Long currentUserId, int offset, int size);

    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.id = #{id}")
    Optional<PostComment> selectByIdWithStats(Long id, Long currentUserId);

    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.parent_id = #{commentId} ORDER BY pc.created_at ASC")
    List<PostComment> selectRepliesByCommentIdWithStats(Long commentId, Long currentUserId);

    // 添加一个简单的测试查询
    @Select("SELECT COUNT(*) FROM post_comment WHERE parent_id = #{commentId}")
    int countRepliesByCommentId(Long commentId);

    @Select("SELECT * FROM post_comment WHERE id = #{id}")
    Optional<PostComment> selectById(Long id);

    @Select("SELECT COUNT(*) FROM post_comment WHERE user_id = #{userId}")
    int countByUserId(Long userId);

    @Select("SELECT COUNT(*) FROM post_comment WHERE post_id = #{postId}")
    int countByPostId(Long postId);

    @Update("UPDATE post_comment SET content = #{content} WHERE id = #{id}")
    int updateById(PostComment comment);

    @Update("UPDATE post_comment SET like_count = #{likeCount} WHERE id = #{id}")
    int updateLikeCount(Long id, Integer likeCount);

    @Delete("DELETE FROM post_comment WHERE id = #{id}")
    int deleteById(Long id);

    @Delete("DELETE FROM post_comment WHERE post_id = #{postId}")
    int deleteByPostId(Long postId);

    @Delete("DELETE FROM post_comment WHERE user_id = #{userId}")
    int deleteByUserId(Long userId);

    /**
     * 根据帖子ID获取前N条评论（快速加载）
     */
    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.post_id = #{postId} AND pc.parent_id IS NULL ORDER BY pc.created_at ASC LIMIT #{limit}")
    List<PostComment> selectByPostIdWithStatsOrderByCreatedAtAscLimit(Long postId, Long currentUserId, Integer limit);

    /**
     * 根据帖子ID分页获取评论（后台补充加载）
     */
    @Select("SELECT pc.*, " +
            "u.username as author, " +
            "COALESCE(cl_stats.like_count, 0) as like_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked " +
            "FROM post_comment pc " +
            "LEFT JOIN user u ON pc.user_id = u.id " +
            "LEFT JOIN (SELECT comment_id, COUNT(*) as like_count FROM comment_like WHERE is_liked = TRUE GROUP BY comment_id) cl_stats ON pc.id = cl_stats.comment_id " +
            "LEFT JOIN (SELECT comment_id, is_liked FROM comment_like WHERE user_id = #{currentUserId}) user_like ON pc.id = user_like.comment_id " +
            "WHERE pc.post_id = #{postId} AND pc.parent_id IS NULL ORDER BY pc.created_at ASC LIMIT #{limit} OFFSET #{offset}")
    List<PostComment> selectByPostIdWithStatsOrderByCreatedAtAscPagination(Long postId, Long currentUserId, Integer offset, Integer limit);
}
